"""基金持仓查询模块"""

TEN_STOCKS_SQL = """
WITH LatestReport AS (
    SELECT 
        MAX(ksp.ReportDate) AS LatestDate
    FROM SecuMain sm
    INNER JOIN MF_KeyStockPortfolio ksp ON sm.InnerCode = ksp.InnerCode
    INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
    WHERE (
          (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
          OR 
          (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
          OR 
          (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
      )
      AND (:start_date IS NULL OR ksp.ReportDate >= TO_DATE(:start_date, 'YYYY-MM-DD'))
      AND (:end_date IS NULL OR ksp.ReportDate <= TO_DATE(:end_date, 'YYYY-MM-DD'))
)
SELECT * FROM (
    SELECT 
        sm.ChiName AS 基金全称,
        fa.SecurityCode AS 基金代码,
        COALESCE(sm_stock.ChiName, hk.ChiName) AS 股票名称,
        COALESCE(sm_stock.ChiNameAbbr, hk.ChiNameAbbr) AS 股票简称,
        COALESCE(sm_stock.SecuCode, hk.SecuCode) AS 股票代码,
        ksp.SharesHolding AS 持股数量_股,
        ksp.ChangeOfSharesHolding AS 较上期持仓量变动_股,
        ksp.MarketValue AS 市值_元,
        TO_CHAR(ROUND(ksp.RatioInNV * 100, 2), 'FM999999990.00') || '%' AS 占资产净值比例,
        ksp.ReportDate AS 报告期
    FROM SecuMain sm
    INNER JOIN MF_KeyStockPortfolio ksp ON sm.InnerCode = ksp.InnerCode
    INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
    INNER JOIN LatestReport lr ON ksp.ReportDate = lr.LatestDate
    LEFT JOIN SecuMain sm_stock 
        ON ksp.StockInnerCode = sm_stock.InnerCode 
       AND ksp.StockInnerCode < 1000000
    LEFT JOIN HK_SecuMain hk 
        ON ksp.StockInnerCode = hk.InnerCode 
       AND ksp.StockInnerCode >= 1000000 
       AND ksp.StockInnerCode < 2000000
    WHERE (
          (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
          OR 
          (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
          OR 
          (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
      )
    ORDER BY ksp.RatioInNV DESC
)
WHERE ROWNUM <= 10
"""

FIVE_BONDS_SQL = """
WITH LatestReport AS (
    SELECT 
        MAX(ksp.ReportDate) AS LatestDate
    FROM SecuMain sm
    INNER JOIN MF_BondPortifolioDetail ksp ON sm.InnerCode = ksp.InnerCode
    INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
    WHERE ksp.IfInConvertibleTerm = 0
      AND (
          (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
          OR 
          (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
          OR 
          (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
      )
      AND (:start_date IS NULL OR ksp.ReportDate >= TO_DATE(:start_date, 'YYYY-MM-DD'))
      AND (:end_date IS NULL OR ksp.ReportDate <= TO_DATE(:end_date, 'YYYY-MM-DD'))
)
SELECT * FROM (
    SELECT 
        sm.ChiName AS 基金全称,
        fa.SecurityCode AS 基金代码,
        sm_bond.ChiName AS 债券名称,
        sm_bond.SecuCode AS 债券代码,
        ksp.HoldVolume AS 持有数量_份,
        ksp.MarketValue AS 持有市值_元,
        TO_CHAR(ROUND(ksp.RatioInNV * 100, 2), 'FM999999990.00') || '%' AS 市值占净资产的比例,
        ksp.ReportDate AS 报告期
    FROM SecuMain sm
    INNER JOIN MF_BondPortifolioDetail ksp ON sm.InnerCode = ksp.InnerCode
    INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
    INNER JOIN LatestReport lr ON ksp.ReportDate = lr.LatestDate
    LEFT JOIN SecuMain sm_bond ON ksp.BondCode = sm_bond.InnerCode 
    WHERE ksp.IfInConvertibleTerm = 0
      AND (
          (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
          OR 
          (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
          OR 
          (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
      )
    ORDER BY ksp.RatioInNV DESC
)
WHERE ROWNUM < 6
"""

ALL_STOCKS_SQL = """
WITH LatestReport AS (
    SELECT 
        MAX(ksp.ReportDate) AS LatestDate
    FROM SecuMain sm
    INNER JOIN MF_StockPortfolioDetail ksp ON sm.InnerCode = ksp.InnerCode
    INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
    WHERE (
          (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
          OR 
          (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
          OR 
          (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
      )
      AND (:start_date IS NULL OR ksp.ReportDate >= TO_DATE(:start_date, 'YYYY-MM-DD'))
      AND (:end_date IS NULL OR ksp.ReportDate <= TO_DATE(:end_date, 'YYYY-MM-DD'))
)
SELECT 
    sm.ChiName AS 基金全称,
    fa.SecurityCode AS 基金代码,
    COALESCE(sm_stock.ChiName, hk.ChiName) AS 股票名称,
    COALESCE(sm_stock.ChiNameAbbr, hk.ChiNameAbbr) AS 股票简称,
    COALESCE(sm_stock.SecuCode, hk.SecuCode) AS 股票代码,
    ksp.SharesHolding AS 持股数量_股,
    ksp.ChangeOfSharesHolding AS 较上期持仓量变动_股,
    ksp.MarketValue AS 市值_元,
    TO_CHAR(ROUND(ksp.RatioInNV * 100, 2), 'FM999999990.00') || '%' AS 占资产净值比例,
    ksp.ReportDate AS 报告期
FROM SecuMain sm
INNER JOIN MF_StockPortfolioDetail ksp ON sm.InnerCode = ksp.InnerCode
INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
INNER JOIN LatestReport lr ON ksp.ReportDate = lr.LatestDate
LEFT JOIN SecuMain sm_stock 
    ON ksp.StockInnerCode = sm_stock.InnerCode 
   AND ksp.StockInnerCode < 1000000
LEFT JOIN HK_SecuMain hk 
    ON ksp.StockInnerCode = hk.InnerCode 
   AND ksp.StockInnerCode >= 1000000 
   AND ksp.StockInnerCode < 2000000
WHERE (
      (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
      OR 
      (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
      OR 
      (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
  )
ORDER BY ksp.RatioInNV DESC
"""

ALL_BONDS_SQL = """
WITH LatestReport AS (
    SELECT 
        MAX(ksp.ReportDate) AS LatestDate
    FROM SecuMain sm
    INNER JOIN MF_BondPortifolioDetail ksp ON sm.InnerCode = ksp.InnerCode
    INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
    WHERE ksp.IfInConvertibleTerm = 0
      AND (
          (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
          OR 
          (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
          OR 
          (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
      )
      AND (:start_date IS NULL OR ksp.ReportDate >= TO_DATE(:start_date, 'YYYY-MM-DD'))
      AND (:end_date IS NULL OR ksp.ReportDate <= TO_DATE(:end_date, 'YYYY-MM-DD'))
)
SELECT 
    sm.ChiName AS 基金全称,
    fa.SecurityCode AS 基金代码,
    sm_bond.ChiName AS 债券名称,
    sm_bond.SecuCode AS 债券代码,
    ksp.HoldVolume AS 持有数量_份,
    ksp.MarketValue AS 持有市值_元,
    TO_CHAR(ROUND(ksp.RatioInNV * 100, 2), 'FM999999990.00') || '%' AS 市值占净资产的比例,
    ksp.ReportDate AS 报告期
FROM SecuMain sm
INNER JOIN MF_BondPortifolioDetail ksp ON sm.InnerCode = ksp.InnerCode
INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
INNER JOIN LatestReport lr ON ksp.ReportDate = lr.LatestDate
LEFT JOIN SecuMain sm_bond ON ksp.BondCode = sm_bond.InnerCode 
WHERE ksp.IfInConvertibleTerm = 0
  AND (
      (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
      OR 
      (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
      OR 
      (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
  )
ORDER BY ksp.RatioInNV DESC
"""

STOCKS_CHANGE_SQL = """
WITH LatestReport AS (
    SELECT 
        MAX(ksp.ReportDate) AS LatestDate
    FROM SecuMain sm
    INNER JOIN MF_StockPortfolioChange ksp ON sm.InnerCode = ksp.InnerCode
    INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
    WHERE (
          (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
          OR 
          (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
          OR 
          (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
      )
      AND (:start_date IS NULL OR ksp.ReportDate >= TO_DATE(:start_date, 'YYYY-MM-DD'))
      AND (:end_date IS NULL OR ksp.ReportDate <= TO_DATE(:end_date, 'YYYY-MM-DD'))
)
SELECT 
    sm.ChiName AS 基金全称,
    fa.SecurityCode AS 基金代码,
    COALESCE(sm_stock.ChiName, hk.ChiName) AS 股票名称,
    COALESCE(sm_stock.ChiNameAbbr, hk.ChiNameAbbr) AS 股票简称,
    COALESCE(sm_stock.SecuCode, hk.SecuCode) AS 股票代码,
    ksp.ChangeType AS 变动方向,
    ksp.AccumulatedTradeSum AS 累计金额_元,
    TO_CHAR(ROUND(ksp.RatioInNVAtBegin * 100, 2), 'FM999999990.00') || '%' AS 占期初基金净值比例,
    ksp.ReportDate AS 报告期
FROM SecuMain sm
INNER JOIN MF_StockPortfolioChange ksp ON sm.InnerCode = ksp.InnerCode
INNER JOIN MF_FundArchives fa ON fa.INNERCODE = sm.INNERCODE 
INNER JOIN LatestReport lr ON ksp.ReportDate = lr.LatestDate
LEFT JOIN SecuMain sm_stock 
    ON ksp.StockInnerCode = sm_stock.InnerCode 
   AND ksp.StockInnerCode < 1000000
LEFT JOIN HK_SecuMain hk 
    ON ksp.StockInnerCode = hk.InnerCode 
   AND ksp.StockInnerCode >= 1000000 
   AND ksp.StockInnerCode < 2000000
WHERE (
      (:fund_code IS NOT NULL AND fa.SecurityCode = :fund_code)
      OR 
      (:fund_name IS NOT NULL AND sm.ChiName LIKE '%' || :fund_name || '%')
      OR 
      (:fund_code IS NULL AND :fund_name IS NULL AND 1=1)
  )
ORDER BY ABS(ksp.AccumulatedTradeSum) DESC
"""

# 持仓查询函数
def get_top_ten_stocks(fund_code=None, fund_name=None, start_date=None, end_date=None):
    """获取十大重仓股"""
    params = {
        'fund_code': fund_code,
        'fund_name': fund_name,
        'start_date': start_date,
        'end_date': end_date
    }
    return TEN_STOCKS_SQL, params

def get_top_five_bonds(fund_code=None, fund_name=None, start_date=None, end_date=None):
    """获取前五大债券持仓"""
    params = {
        'fund_code': fund_code,
        'fund_name': fund_name,
        'start_date': start_date,
        'end_date': end_date
    }
    return FIVE_BONDS_SQL, params

def get_all_stocks(fund_code=None, fund_name=None, start_date=None, end_date=None):
    """获取全部股票持仓"""
    params = {
        'fund_code': fund_code,
        'fund_name': fund_name,
        'start_date': start_date,
        'end_date': end_date
    }
    return ALL_STOCKS_SQL, params

def get_all_bonds(fund_code=None, fund_name=None, start_date=None, end_date=None):
    """获取全部债券持仓"""
    params = {
        'fund_code': fund_code,
        'fund_name': fund_name,
        'start_date': start_date,
        'end_date': end_date
    }
    return ALL_BONDS_SQL, params

def get_stocks_change(fund_code=None, fund_name=None, start_date=None, end_date=None):
    """获取股票持仓变动"""
    params = {
        'fund_code': fund_code,
        'fund_name': fund_name,
        'start_date': start_date,
        'end_date': end_date
    }
    return STOCKS_CHANGE_SQL, params